package com.cmbdb.job;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;
import java.util.List;

/**
 * 注释
 *
 * @author: Chu, Si-Qi
 * @Date: 16-12-29
 * @Time: 下午5:26
 * 用于处理Excel文件
 */
public class ExcelUtil {

    public void readExcel(String filePath){
        Workbook workbook = null;
        try {
            workbook = getExcelWorkbook(filePath);

            for (int s = 1; s <= workbook.getNumberOfSheets(); s++) {

                System.out.println("-------------------------------------------------------------第" + s + "页--------------------------------------------------------------------");

                Sheet sheet = getSheetByNum(workbook, s);

                int lastRowNum = sheet.getLastRowNum();

                Row row = null;
                for (int i = 0; i <= lastRowNum; i++) {
                    row = sheet.getRow(i);
                    if (row != null) {
                        int lastCellNum = row.getLastCellNum();
                        Cell cell = null;
                        StringBuffer sb = new StringBuffer();
                        for (int j = 0; j <= lastCellNum; j++) {
                            cell = row.getCell(j);
                            if (cell != null) {
                                int type = cell.getCellType();
                                switch (type) {
                                    case 0:
                                        if (DateUtil.isCellDateFormatted(cell)) {
                                            sb.append("第" + i + "行/第" + j + "列/类型为Date值为：");
                                            sb.append(cell.getDateCellValue() + "\n");
                                        } else {
                                            sb.append("第" + i + "行/第" + j + "列/类型为Numeric值为：");
                                            sb.append(cell.getNumericCellValue() + "\n");
                                        }
                                        break;
                                    case 1:
                                        sb.append("第" + i + "行/第" + j + "列/类型为String值为：");
                                        sb.append(cell.getStringCellValue() + "\n");
                                        break;
                                    case 2:
                                        sb.append("第" + i + "行/第" + j + "列/类型为Formula值为：");
                                        sb.append(cell.getCellFormula() + "\n");
                                        break;
                                    case 3:
                                        sb.append("第" + i + "行/第" + j + "列/类型为Blank值为：");
                                        sb.append(cell.getStringCellValue() + "\n");
                                        break;
                                    case 4:
                                        sb.append("第" + i + "行/第" + j + "列/类型为Boolean值为：");
                                        sb.append(cell.getBooleanCellValue() + "\n");
                                        break;
                                    case 5:
                                        sb.append("第" + i + "行/第" + j + "列/类型为Error值为：");
                                        sb.append(cell.getErrorCellValue() + "\n");
                                        break;
                                    default:
                                        break;
                                }
                            }
                        }
                        System.out.println(sb);
                    }
                }
            }

            Map<String,String> map = new HashMap<String,String>();
            List<Map<String,String>> list = new LinkedList<Map<String,String>>();

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public Workbook getExcelWorkbook(String filePath) throws IOException {
        Workbook workbook = null;
        File file = null;
        FileInputStream fileInputStream = null;
        file = new File(filePath);
        if(!file.exists()){
            throw new RuntimeException("文件不存在！");
        }else{
            fileInputStream = new FileInputStream(file);
            try {
                workbook = WorkbookFactory.create(fileInputStream);
            } catch (IOException e) {
                throw new RuntimeException(e.getMessage());
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            } finally {
                if(fileInputStream != null){
                    fileInputStream.close();
                }
            }
        }
        return workbook;
    }

    public Sheet getSheetByNum(Workbook workbook, int number){
        Sheet sheet = null;
        try {
            sheet = workbook.getSheetAt(number - 1);
        }catch (Exception e){
            throw new RuntimeException(e.getMessage());
        }
        return sheet;
    }

    public static void main(String args[]){
        ExcelUtil excelUtil = new ExcelUtil();
        excelUtil.readExcel("C:\\Users\\Dell\\Desktop\\aaa.xlsx");
    }
}